-----------------------------------------------------------------------------
-- Tim Kiem Khach Hang
--1: MaKhachHang
--2: TenKhachHang
--3: DiaChiLienLac
--4: SoDienThoai
--5: LoaiKhachHang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TimKiemKhachHang') IS NOT NULL
DROP PROC sp_TimKiemKhachHang
GO

CREATE PROCEDURE sp_TimKiemKhachHang 
	@chuoiTimKiem nvarchar(150),
	@timKiemTheo int 
AS 
BEGIN
	If(@timKiemTheo = 1)		
		SELECT MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		WHERE MaKhachHang = @chuoiTimKiem 	
	else if (@timKiemTheo = 2)		
		SELECT MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		WHERE TenKhachHang like '%' + @chuoiTimKiem + '%'
	else if (@timKiemTheo = 3)
		SELECT MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		WHERE DiaChiLienLac like '%' + @chuoiTimKiem + '%'
	else if (@timKiemTheo = 4)
		SELECT MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		WHERE SoDienThoai = @chuoiTimKiem 
	else if (@timKiemTheo = 5)
		SELECT MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		WHERE LoaiKhachHang = @chuoiTimKiem 
END
GO
-----------------------------------------------------------------------------------

---------------------------------------------------------------------------------
IF OBJECT_ID('sp_LayDanhSachKhachHang') IS NOT NULL
	DROP PROC sp_LayDanhSachKhachHang
GO

CREATE PROCEDURE sp_LayDanhSachKhachHang 
AS 
BEGIN

		SELECT MaKhachHang, TenKhachHang, SoDienThoai, LoaiKhachHang
		FROM KhachHang
		
END
GO
-----------------------------------------------------------------------------
-- Tim Kiem Don Hang
-- 1: MaDonHang
-- 2: MaKhachHang
-- 3: NgayNhan
-- 4: NgayGiao
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TimKiemThongTinDonHang') IS NOT NULL
DROP PROC sp_TimKiemThongTinDonHang
GO

CREATE PROCEDURE sp_TimKiemThongTinDonHang 
	@chuoiTimKiem nvarchar(25),
	@timKiemTheo int 
AS 
BEGIN
	If(@timKiemTheo = 1)		
		SELECT ttdh.MaDonHang, kh.MaKhachHang, ttdh.NgayNhan, ttdh.NgayGiao
		FROM ThongTinDonHang ttdh 
		INNER JOIN KhachHang kh
		On(ttdh.MaKhachHang = kh.MaKhachHang)
		WHERE MaDonHang = @chuoiTimKiem 	
	else if (@timKiemTheo = 2)		
		SELECT ttdh.MaDonHang, kh.TenKhachHang, ttdh.NgayNhan, ttdh.NgayGiao
		FROM ThongTinDonHang ttdh 
		INNER JOIN KhachHang kh
		On(ttdh.MaKhachHang = kh.MaKhachHang)
		WHERE kh.TenKhachHang like '%' + @chuoiTimKiem + '%' 	
	else if (@timKiemTheo = 3)
		SELECT ttdh.MaDonHang, kh.TenKhachHang, ttdh.NgayNhan, ttdh.NgayGiao
		FROM ThongTinDonHang ttdh 
		INNER JOIN KhachHang kh
		On(ttdh.MaKhachHang = kh.MaKhachHang)
		WHERE NgayNhan = @chuoiTimKiem 	
	else if (@timKiemTheo = 4)
 	
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Tim Kiem Don Hang
-- 1: MaDonHang
-- 2: TenKhachHang
-- 3: NgayNhan
-- 4: NgayGiao
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TimKiemTheoMaSanPham') IS NOT NULL
	DROP PROC sp_TimKiemTheoMaSanPham
GO

CREATE PROCEDURE sp_TimKiemTheoMaSanPham 
	@maSanPham nvarchar(25)
AS 
BEGIN
	SELECT TenSanPham, KichThuoc, HinhVe, SoMau, MaRap, MaKhuon, MaNapChup, DoKho
	FROM ThongTinSanPham
	WHERE MaSanPham = @maSanPham
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Tim Kiem Don Hang
-- 1: MaCongNhan
-- 2: TenCongNhan
-- 3: BacCongNhan
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TimKiemCongNhan') IS NOT NULL
	DROP PROC sp_TimKiemCongNhan
GO

CREATE PROCEDURE sp_TimKiemCongNhan 
	@timKiem nvarchar(50),
	@timKiemTheo int
AS 
BEGIN
	IF(@timKiemTheo = 1)
		SELECT MaCongNhan, TenCongNhan, BacCongNhan
		FROM CongNhan
		WHERE MaCongNhan = @timKiem
	ELSE IF(@timKiemTheo = 2)
		SELECT MaCongNhan, TenCongNhan, BacCongNhan
		FROM CongNhan
		WHERE TenCongNhan like '%' + @timKiem + '%'
	ELSE IF(@timKiemTheo = 3)
		SELECT MaCongNhan, TenCongNhan, BacCongNhan
		FROM CongNhan
		WHERE BacCongNhan = CAST(@timKiem AS int)
END
GO

EXEC sp_TimKiemCongNhan 2,"2";
-----------------------------------------------------------------------------------




------------------------------------------------------------------------------------
IF OBJECT_ID('sp_LayDanhSachCongNhan') IS NOT NULL
	DROP PROC sp_LayDanhSachCongNhan
GO

CREATE PROCEDURE sp_LayDanhSachCongNhan 
AS 
BEGIN

		SELECT MaCongNhan, TenCongNhan, BacCongNhan
		FROM CongNhan
		
END
GO



-----------------------------------------------------------------------------
-- Xoa San Pham ra khoi don hang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_XoaSanPhamTheoDonHang') IS NOT NULL
	DROP PROC sp_XoaSanPhamTheoDonHang
GO

CREATE PROCEDURE sp_XoaSanPhamTheoDonHang 
	@maDonhang nvarchar(25),
	@maSanPham nvarchar(25)
AS 
BEGIN
	DELETE FROM DonHang WHERE MaDonHang = @maDonhang AND MaSanPham = @maSanPham
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Tao Don Hang Moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TaoDonHang') IS NOT NULL
	DROP PROC sp_TaoDonHang
GO

CREATE PROCEDURE sp_TaoDonHang 
	@MaDonHang nvarchar(25),
	@MaKhachHang nvarchar(25),
	@DiaChiGiaoHang nvarchar(100),
	@NgayNhan datetime,
	@MaSanPham nvarchar(25),
	@SoLuong int,
	@QuyCachPallet nvarchar(50)
AS 
BEGIN
	INSERT INTO ThongTinDonHang(MaDonHang, MaKhachHang, DiaChi, NgayNhan)
	VALUES(@MaDonHang, @MaKhachHang, @DiaChiGiaoHang, @NgayNhan);

	INSERT INTO DonHang(MaDonHang, MaSanPham, SoLuong, QuyCachPallet)
	VALUES(@MaDonHang, @MaSanPham, @SoLuong, @QuyCachPallet);
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Tao Khach hang Moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TaoKhachHang') IS NOT NULL
	DROP PROC sp_TaoKhachHang
GO

CREATE PROCEDURE sp_TaoKhachHang 
	@MaKhachHang nvarchar(25),
	@TenKhachHang nvarchar(50),
	@DiaChiLienLac nvarchar(150),
	@SoDienThoai nvarchar(18),
	@LoaiKhachHang  nvarchar(20)
AS 
BEGIN
	INSERT INTO KhachHang(MaKhachHang, TenKhachHang, DiaChiLienLac, SoDienThoai, LoaiKhachHang)
	VALUES(@MaKhachHang, @TenKhachHang, @DiaChiLienLac, @SoDienThoai, @LoaiKhachHang);	
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- update thong tin khach hang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_capNhatThongTinKhachHang') IS NOT NULL
	DROP PROC sp_capNhatThongTinKhachHang
GO

CREATE PROCEDURE sp_capNhatThongTinKhachHang 
	@MaKhachHang nvarchar(25),
	@TenKhachHang nvarchar(50),
	@DiaChiLienLac nvarchar(150),
	@SoDienThoai nvarchar(18),
	@LoaiKhachHang  nvarchar(20)
AS 
BEGIN
	UPDATE KhachHang
	SET TenKhachHang = @TenKhachHang, 
		DiaChiLienLac = @DiaChiLienLac, 
		SoDienThoai = @SoDienThoai, 
		LoaiKhachHang = @LoaiKhachHang
	WHERE MaKhachHang = @MaKhachHang
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- tao san pham moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_TaoSanPham') IS NOT NULL
	DROP PROC sp_TaoSanPham
GO

CREATE PROCEDURE sp_TaoSanPham 
	@MaSanPham nvarchar(25),
	@TenSanPham nvarchar(25),
	@KichThuoc nvarchar(25),
	@HinhVe nvarchar(100),
	@SoMau int,
	@MaRap nvarchar(25),
	@MaKhuon nvarchar(25),
	@MaNapChup nvarchar(25),
	@DoKho int
AS 
BEGIN
	INSERT INTO ThongTinSanPham(MaSanPham, TenSanPham, KichThuoc, HinhVe, SoMau, MaRap, MaKhuon, MaNapChup, DoKho)
	VALUES(@MaSanPham, @TenSanPham, @KichThuoc, @HinhVe, @SoMau, @MaRap, @MaKhuon, @MaNapChup, @DoKho)
END
GO
--------------------------------------------------------
---------------------------



-----------------------------------------------------------------------------
-- update thong tin don hang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_capNhatThongTinDonHang') IS NOT NULL
	DROP PROC sp_capNhatThongTinDonHang
GO

CREATE PROCEDURE sp_capNhatThongTinDonHang 
	@MaDonHang nvarchar(25),	
	@MaSanPham nvarchar(25),
	@SoLuong int,
	@QuyCachPallet nvarchar(50),
	@QuyCachDongGoi nvarchar(50)
AS 
BEGIN
	UPDATE DonHang
	SET SoLuong = @SoLuong,
		QuyCachPallet = @QuyCachPallet,
		QuyCachDongGoi = @QuyCachDongGoi
	WHERE MaDonHang = @MaDonHang AND MaSanPham = @MaSanPham
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Lay so luong san xuat nguyen lieu
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_laySoLuongSanXuatNguyenLieu') IS NOT NULL
	DROP PROC sp_laySoLuongSanXuatNguyenLieu
GO

CREATE PROCEDURE sp_laySoLuongSanXuatNguyenLieu 
	@MaDonHang nvarchar(25), 
	@NgayNhan datetime
AS 
BEGIN
	SELECT b.MaBanThanhPham, btp.SoLuongTonKho
	FROM DonHang dh
	INNER JOIN BOM b ON (dh.MaSanPham =  b.MaSanPham)
	INNER JOIN BanThanhPham btp ON (btp.MaBanThanhPham = b.MaBanThanhPham)
	WHERE dh.MaDonHang = @MaDonHang
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Danh sach cong nhan
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_layDanhSachCongNhan') IS NOT NULL
	DROP PROC sp_layDanhSachCongNhan
GO

CREATE PROCEDURE sp_layDanhSachCongNhan
AS 
BEGIN
	SELECT MaCongNhan, TenCongNhan, BacCongNhan
	FROM CongNhan
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Them Cong Nhan Vao San Xuat Don Hang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_themVaoTheoDoiCongNhan') IS NOT NULL
	DROP PROC sp_themVaoTheoDoiCongNhan
GO

CREATE PROCEDURE sp_themVaoTheoDoiCongNhan
	@MaCongNhan nvarchar(25),
	@MaSanPham nvarchar(25),
	@MaDonHang nvarchar(25)
AS 
BEGIN
	INSERT INTO TheoDoiCongNhan(MaCongNhan, MaSanPham, MaDonHang)
	VALUES(@MaCongNhan, @MaSanPham, @MaDonHang)
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- Tim kiem thong tin rap theo ma rap
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_timKiemThongTinRapTheoMaRap') IS NOT NULL
	DROP PROC sp_timKiemThongTinRapTheoMaRap
GO

CREATE PROCEDURE sp_timKiemThongTinRapTheoMaRap
	@MaRap nvarchar(25)
AS 
BEGIN
	SELECT
	Rap.MaRap,
	Rap.SoLuong,
	Rap.HinhAnh,
	Rap.SoLuongDangSuDung
	FROM RAP
	WHERE MaRap = @MaRap
END
GO
-----------------------------------------------------------------------------------



-----------------------------------------------------------------------------
-- Tao rap moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_taoRap') IS NOT NULL
	DROP PROC sp_taoRap
GO

CREATE PROCEDURE sp_taoRap
	@MaRap nvarchar(25),
	@SoLuong int,
	@HinhAnh nvarchar(25),
	@KichThuoc nvarchar(25),
	@TenRap nvarchar(25)
AS 
BEGIN
	INSERT INTO RAP(MaRap, SoLuong, HinhAnh, KichThuoc, TenRap)
	VALUES(@MaRap, @SoLuong, @HinhAnh, @KichThuoc, @TenRap)
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- Tim kiem thong tin khuon theo ma khuon
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_timKiemThongTinKhuonTheoMaKhuon') IS NOT NULL
	DROP PROC sp_timKiemThongTinKhuonTheoMaKhuon
GO

CREATE PROCEDURE sp_timKiemThongTinKhuonTheoMaKhuon
	@MaKhuon nvarchar(25)
AS 
BEGIN
	SELECT
	Khuon.MaKhuon,
	Khuon.SoLuong,
	Khuon.HinhAnh,
	Khuon.SoLuongDangSuDung
	FROM Khuon
	WHERE MaKhuon = @MaKhuon
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- Tim kiem thong tin nap chup theo ma nap chup
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_timKiemThongTinNapChupTheoMaNapChup') IS NOT NULL
	DROP PROC sp_timKiemThongTinNapChupTheoMaNapChup
GO

CREATE PROCEDURE sp_timKiemThongTinNapChupTheoMaNapChup
	@MaNapChup nvarchar(25)
AS 
BEGIN
	SELECT
	MaNapChup,
	SoLuong,
	HinhAnh,
	SoLuongDangSuDung
	FROM NapChup
	WHERE MaNapChup = @MaNapChup
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- tao khuon moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_taoKhuon') IS NOT NULL
	DROP PROC sp_taoKhuon
GO

CREATE PROCEDURE sp_taoKhuon
	@MaKhuon nvarchar(25),
	@SoLuong int,
	@HinhAnh nvarchar(25),
	@KichThuoc nvarchar(25),
	@TenKhuon nvarchar(25)
AS 
BEGIN
	INSERT INTO Khuon(MaKhuon, SoLuong, HinhAnh, KichThuoc, TenKhuon)
	VALUES(@MaKhuon, @SoLuong, @HinhAnh, @KichThuoc, @TenKhuon)
END
GO
-----------------------------------------------------------------------------------





-----------------------------------------------------------------------------
-- tao nap chup moi
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_taoNapChup') IS NOT NULL
	DROP PROC sp_taoNapChup
GO

CREATE PROCEDURE sp_taoNapChup
	@MaNapChup nvarchar(25),
	@SoLuong int,
	@HinhAnh nvarchar(25),
	@KichThuoc nvarchar(25),
	@TenNapChup nvarchar(25)
AS 
BEGIN
	INSERT INTO NapChup(MaNapChup, SoLuong, HinhAnh, KichThuoc, TenNapChup)
	VALUES(@MaNapChup, @SoLuong, @HinhAnh, @KichThuoc, @TenNapChup)
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- tao theo doi cong nhan
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_taoTheoDoiCongNhan') IS NOT NULL
	DROP PROC sp_taoTheoDoiCongNhan
GO

CREATE PROCEDURE sp_taoTheoDoiCongNhan
	@NgayTheoDoi datetime,
	@MaCongNhan nvarchar(25),
	@MaSanPham nvarchar(25),
	@SanLuong int,
	@PhePham int
AS 
BEGIN
	INSERT INTO TheoDoiCongNhan(NgayTheoDoi, MaCongNhan, MaSanPham, SanLuong, PhePham)
	VALUES(@NgayTheoDoi, @MaCongNhan, @MaSanPham, @SanLuong, @PhePham)
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- tim kiem chi tet don hang
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_timKiemChiTietDonHang') IS NOT NULL
	DROP PROC sp_timKiemChiTietDonHang
GO

CREATE PROCEDURE sp_timKiemChiTietDonHang
	@MaDonHang nvarchar(25)	
AS 
BEGIN
	SELECT
		DonHang.MaDonHang, DonHang.MaSanPham, DonHang.SoLuong, DonHang.SoLuongSanXuat, LoiDonHang.TongSoLoi
	FROM DonHang
	INNER JOIN LoiDonHang ON (DonHang.MaDonHang =  LoiDonHang.MaDonHang)
END
GO
-----------------------------------------------------------------------------------




-----------------------------------------------------------------------------
-- tim kiem BOM
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_timKiemBOM') IS NOT NULL
	DROP PROC sp_timKiemBOM
GO

CREATE PROCEDURE sp_timKiemBOM
	@MaSanPham nvarchar(25)	
AS 
BEGIN
	SELECT
		MaBanThanhPham, MoTa, KhoiLuong
	FROM BOM
	WHERE MaSanPham = @MaSanPham
END
GO
-----------------------------------------------------------------------------------


-----------------------------------------------------------------------------
-- update loi, kiem tra khuyet tat
-----------------------------------------------------------------------------
IF OBJECT_ID('sp_capNhatKhuyetTatBanThanhPhamGachBong') IS NOT NULL
	DROP PROC sp_capNhatKhuyetTatBanThanhPhamGachBong
GO

CREATE PROCEDURE sp_capNhatKhuyetTatBanThanhPhamGachBong
	@NgaySanXuat datetime,
	@NgayKiemTra datetime,
	@MaCongNhan nvarchar(25),
	@MaSanPham nvarchar(25),
	@MaDonHang nvarchar(25),
	@MaLoi nvarchar(25),
	@LoaiLoi nvarchar(25),
	@SoLoi int
AS 
BEGIN
	UPDATE LoiDonHang
	SET NgaySanXuat = @NgaySanXuat,
		NgayKiemTra = @NgayKiemTra
	WHERE LoiDonHang.MaDonHang = @MaDonHang AND LoiDonHang.MaSanPham = @MaSanPham;

	UPDATE TheoDoiCongNhan
	SET MaCongNhan = @MaCongNhan,
		MaSanPham = @MaSanPham,
		MaDonHang = @MaDonHang,
		SoLoi = @SoLoi
	WHERE TheoDoiCongNhan.MaDonHang = @MaDonHang AND TheoDoiCongNhan.MaSanPham = @MaSanPham;

	UPDATE ChiTietLoi
	SET LoaiLoi = @LoaiLoi
	WHERE ChiTietLoi.MaLoi = @MaLoi;
END
GO
-----------------------------------------------------------------------------------